package com.ruoyi.common.utils.sql;

import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author anan.
 * @version 1.0
 * @create 2021-04-13 13-55
 */
public class SqlReplaceUtil {

    /** 以 @为起点,空格或单引号结尾的变量 */
    public static String MUST_VARIABLE = "@([^[A-Za-z][A-Za-z1-9_]+$]*)";
    public static String OPTIONAL_VARIABLE = "\\[([^\\]]*)";

    /**
     * 往SQL语句里变量,通用于所有参数注入.
     * @param sql
     * @param map
     * @return
     */
    public static String getSql(String sql,Map map){
        String rStr = sqlPush(sql,map);
        rStr = sqlFilter(rStr,map);
        return rStr;
    }

    /**
     * @变量注入
     * @param sql
     * @param map
     * @return
     */
    public static String sqlPush(String sql, Map map) {
        List list = getContext(sql, MUST_VARIABLE);
        String para;
        String value;
        String rSql = sql;
        for (int i = 0; i < list.size(); i++) {
            para = (String) list.get(i);
            value = String.valueOf(map.get(para)) ;
            if (!"null".equals(value)) {
                rSql = rSql.replaceAll("@" + para, value);
            }
        }
        /** \\d+.\\d+|\\w+"; */
        return rSql;
    }

    public static TreeMap sqlPar(String sql) {
        List list = getContext(sql, MUST_VARIABLE);
        String para;
        String rSql = sql;
        String and;
        TreeMap<String, String> findK = new TreeMap<String, String>(
                new Comparator<String>() {
                    @Override
                    public int compare(String obj1, String obj2) {
                        // 升序升
                        return obj1.compareTo(obj2);
                    }
                });
        for (int i = 0; i < list.size(); i++) {
            para = (String) list.get(i);
            findK.put(para, "");
        }
        String[] sqlf = sql.split(" # ");
        if (sqlf.length > 0) {
            rSql =  sqlf[0];
            for (int i = 1; i < sqlf.length; i++) {
                and = sqlf[i];
                sqlf[i] ="";
                list = getContext(and,OPTIONAL_VARIABLE);
                if (list.size()>0){
                    for (int l =0;l<list.size();l++){
                        para = (String) list.get(l);
                        findK.put(para, "");
                    }
                }
            }
        }
        /**  //\\d+.\\d+|\\w+" */
        return findK;
    }

    /**
     * sql注入如果发现在字段没有加单引号,可以是由于在order by之后做排序字段
     * 作用sql字段注射
     * @param sql
     * @param map
     * @return
     */
    public static String sqlFilter(String sql, Map map) {
        String para;
        String value;
        String rSql = "";
        String key1 = "";
        String and ="";
        String[] sqlf = sql.split(" # ");
        if (sqlf.length > 0) {
            rSql =  sqlf[0];
            for (int i = 1; i < sqlf.length; i++) {
                and = sqlf[i];
                sqlf[i] ="";
                List list = getContext(and,OPTIONAL_VARIABLE);
                if (list.size()>0){
                    for (int l =0;l<list.size();l++){
                        para = (String) list.get(l);
                        value = String.valueOf(map.get(para)) ;
                        if (!"null".equalsIgnoreCase(value) && !"undefined".equalsIgnoreCase(value)){
                            key1 = "\\[" + para	+ "\\]";
                            if (and.contains(" in ")){
                                sqlf[i] = and.replaceAll(key1, "'"+value.replaceAll(",", "','")+"'");
                            }else if (and.contains("order by ")){
                                sqlf[i] = and.replaceAll(key1,value);
                            }else{
                                sqlf[i] = and.replaceAll(key1,"'"+value+"'");
                            }
                        }
                    }
                }else{
                    sqlf[i] = and;
                }
                if (sqlf[i].length() > 3) {
                    rSql += sqlf[i];
                }
            }
        }
        return rSql;
    }

    /**
     * 传入字符串找出正则匹配的变量
     * @param sql 传入字符串
     * @param variable 正则
     * @return
     */
    private static List getContext(String sql, String variable) {
        List resultList = new ArrayList();
        Pattern pattern = Pattern.compile(variable);
        Matcher matcher = pattern.matcher(sql);
        while (matcher.find()) {
            resultList.add(matcher.group(1));
        }
        return resultList;
    }
}
